﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Transactions;

namespace ADO.NET_Entity_Framework
{
    public class Program
    {
        /// <summary>
        /// 3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
        /// </summary>
        public static IQueryable<Customer> FindCustomersLINQ(
            int year, string shippedCountry)
        {
            NorthwindEntities context = new NorthwindEntities();
            IQueryable<Customer> customers = context.Customers.Include("Orders").Where(
                c => c.Orders.Any(
                    o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada"));
            return customers;
        }

        /// <summary>
        /// 4. Implement previous by using native SQL query and executing it through the ObjectContext.
        /// </summary>
        public static ObjectResult<Customer> FindCustomersSQL( 
            int year, string shippedCountry)
        {
            NorthwindEntities context = new NorthwindEntities();
            SqlParameter sqlYear = new SqlParameter("@year", year);
            SqlParameter sqlShippedCountry = new SqlParameter("@country", shippedCountry);
            ObjectResult<Customer> customers = context.ExecuteStoreQuery<Customer>(
                "SELECT * " +
                "FROM Customers AS c " +
                "WHERE EXISTS( " +
                    "SELECT * " +
                    "FROM Orders AS o " +
                    "WHERE c.CustomerID = o.CustomerID AND " +
                    "YEAR(o.ShippedDate) = @year AND " +
                    "o.ShipCountry = @country) ", sqlYear, sqlShippedCountry);
            return customers;
        }

        /// <summary>
        /// 5. Write a method that finds all the sales by specified 
        /// region and period (start / end dates).
        /// </summary>
        public static IQueryable<Order> FindSales(
            string region, DateTime startDate, DateTime endDate)
        {
            NorthwindEntities context = new NorthwindEntities();
            return context.Orders.Where(o => o.ShipRegion == region && 
                o.ShippedDate >= startDate &&
                o.ShippedDate <= endDate);
        }

        /// <summary>
        /// Create a stored procedures in the Northwind database for finding the total 
        /// incomes for given supplier name and period (start date, end date). 
        /// Implement a C# method that calls the stored procedure and returns the 
        /// retuned record set.
        /// </summary>
        public static void GetIncomesExample()
        {
            using (NorthwindEntities context = new NorthwindEntities())
            {
                using (TransactionScope transaction = new TransactionScope())
                {
                    double? result = context.usp_FindTotalIncomes(
                        "Exotic Liquids", new DateTime(1900, 1, 1),
                        new DateTime(2010, 1, 1)).First();
                    Console.WriteLine(result);
                }
            }
        }

        public static void Main()
        {
            // 1. Create a ObjectContext for the Northwind database
            NorthwindEntities context = new NorthwindEntities();
        }
    }
}
